x1000zfandomcom-20200213-history
Background Processes
The work done in the database is carried out with the help of background processes. Both mandatory and optional ones. For all process, use dynamic performance view V$BGPROCESS. Mandatory Log Writer (LGWR) Log Writer writes the committed changed data to online redo log files, in this way the data is never lost. Log Writer is start when *Commit *1 MB Data in it *1/3 Full *Database Checkpoint *Every 3 seconds. Database Writer (DBWN) (can be multiple) Database Writer writes dirty buffers, pinned buffers and free buffers to the data files. It is set by the parameter DB_WRITER_PROCESSES. It can be upto 20 but it is not recommended. It starts when; *Search too long for a free buffers. *Too many dirty buffers *Checkpoint *Dropping undo segments *Any shutdown other than shutdown abort *Tablespace taken read only, offline or in backup mode. Checkpoints (CKPT) Checkpoints can have a book dedicated to them but I am going to summarize these checkpoints here. Checkpoints update the header of the control file, data files and the datafiles that are taken offline or read only in the control files to the latest SCN. In this way, they keep track of what needs to be written from the redo log files to the data files, for this case, the distance between the checkpoint position and end of redo log group should not be more than 90% of the redo log group. Also this is governed by the parameter FAST_START_MTTR_TARGET. FAST_START_MTTR_TARGET it the time taken for instance recovery, which is by default 0 and at the max 1 hour. Setting it too a low value or zero values confirms that redo writes will shortly wait or wont wait for writes to the data files, similarly a high value will result in an unacceptable time. This parameter can be over written by; *LOG_CHECKPOINT_TIMEOUT; used for the timing of writes to the data files. *FAST_START_IO_TARGET; replaces time by number of input/outputs. Checkpoints are initiated when whenever a redo log file is full or a log switch occurs, whenever a tablespace is taken offline, read only or in backup and whenever you normally shutdown the database. PMON Process monitor work is divided into the following; *It restarts stopped background process *It cleans up failed user process and resets the active transaction table and removes the process id from the table. It sets all the locks held by the user free. *It can be made to call by the user. *It tells the listener about the dispatcher load. SMON System monitor does the following; *It uses the redo log files for instance recovery. *It COALESCE contiguous free space in dictionary tablespace. *It cleans up no longer required temporary segments in tablespaces. *If any transactions were missed, they are recovered when data file is brought online. OPTIONAL Archive Writer (ARCN) (Can be multiple) Archive writters are copies of redo log files and are stored in locations specified by some parameters; *LOG_ARCHIVE_DEST_N (you can customize it) *USE_DB_RECOVERY_FILE_DEST (in the flashback recovery area, set by oracle as a safe copy) Archive writes can after a redo log file is full, no process can overwrite this archiving until it is done. Also it can only be done when in archive mode, which can be done as follows; #STARTUP MOUNT #ALTER DATABASE ARCHIVELOG/NOARCHIVELOG #ALTER DATABASE OPEN Other parameters important are; *LOG_ARCHIVE_MIN_SUCCEDD_DEST *LOG_ARCHIVE_FORMAT (which is like reset log id ®%databaseid(d)%threadnumber(t)%logsequencenumber(l).dbf) Memory Monitor (MMON) and Memory Monitor Light (MMNL) Both work for the AWR, waking up every 60 minutes and collecting stats from dynamic views, dictionary tables and optimized query stats and sga and storin it in the AWR table in the SYSAUX tablespace owned by SYSMAN. Dispatchers (DNNN) and Shared Server Processes (SNNN) Related to the shared server connections and the number of dispatches and shared servers. Parallel Query Slaves (QNNN) Used by worker process during external table operations. ASM DISK (ASMB) and ASM BALANCE (RBAL) RBAL coordinates rebalance of activity, both are used for ASM disks. Job Queue Monitor (CJQN) and Job Query (JNNN) Job Queue Monitor gives jobs to job query which executes them. Event Monitor (EMNC) Responsible to manage events Diagnosibility (DIAG and DIA0) DIAG performs diagnostic dumos and DI0 is related to hang detection and dead lock resolution. Memory Manager (MMAN) It is used by ASMM to size each SGA component. Recoverer (RECO) It recovers failed transactions in multiple databases. Queue Monitor (QMNN) It is used to monitor messages in the message queue when oracle advance queue feature is used. Recovery Writer (RVWR) It used to write to recovery info to disk when flashback database is on. Change Tracking Writer (CTWR) It is used when block change tracking policy is used.